/*****************************************
 *  所有版权(c) 上海塔齐通讯技术有限公司       *
 *****************************************/
package com.touch.sysif.sms.api.client;

import com.touch.sysif.sms.api.model.SmsMessage;
import com.touch.sysif.sms.api.model.SmsReport;
import com.touch.sysif.sms.api.model.SubSmsMessage;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 
 * @version $Revision: 1.0 $ 
 * @author yanxiao
 * @date: Jan 17, 2014
 * @time: 10:40:03 PM
 */
public class DbSmsClient extends AbstractSmsClient{
	private Connection conn;
	private String host;
	private String dbname;
	private String url;
	private String apiId;
	
	@Override
	public void init(String configFile) {
		super.init(configFile);
		host= config.getProperty("db.host");
		dbname = config.getProperty("db.dbname");
		url = "jdbc:mysql://"+host+"/"+dbname+"?useUnicode=true&characterEncoding=utf8";
		apiId = username;
		initConn();
	}
	
	public synchronized void initConn(){
		try {
			if(this.conn ==null || conn.isClosed()){
				if(conn !=null ){
					conn.close();
				}
				Class.forName("com.mysql.jdbc.Driver");
				conn = DriverManager.getConnection(url, username, password);
			}
			conn.setAutoCommit(false);
		} catch (Exception e) {
			throw new RuntimeException("DB接口初始化失败，请联系管理员:"+e.getMessage());
		}
	}
	
	/**
	 * @see com.touch.sysif.sms.api.SmsClient#activeTest()
	 */
	public void activeTest() {
		if(conn != null){
			Statement st = null;
			try {
				st = conn.createStatement();
				st.execute("select 1");
			} catch (Exception e) {
				initConn();
			}finally{
				if(st !=null){
					try {
						st.close();
					} catch (SQLException e) {
					}
				}
			}
		}
	}

	public String[] batchSend(SmsMessage[] message) {
		return batchSend(apiId, message);
	}
	
	public String[] batchSend(String apiId , SmsMessage[] message) {
		initConn();
		send(apiId, message);
		List<String> batchIdList = new ArrayList<String>();
		for (SmsMessage smsMessage : message) {
			batchIdList.add(smsMessage.getBatchId());
		}
		return batchIdList.toArray(new String[batchIdList.size()]);
	}
	
	private synchronized void send(String apiId , SmsMessage ... message){
		PreparedStatement ps = null;
		try {
			if(conn == null){
				throw new RuntimeException("无法连接短信服务器!");
			}
			String sql = "INSERT INTO sms_api_mt_"+apiId+" (batchId, reqReport, sendTime, sourceAddr, destAddr, content, smsParam) VALUES (?, ?, ?, ?, ?, ?, ?)";
			ps = this.conn.prepareStatement(sql);
			for (SmsMessage smsMessage : message) {
				String batchId = smsMessage.getBatchId();
				if(batchId == null){
					batchId = generateUUID();
					smsMessage.setBatchId(batchId);
				}else if(batchId.length() !=32){
					batchId = generateUUID();
					System.out.println("警告！请设置batchId 为32的UUID,已经自动将"+smsMessage.getBatchId()+"替换为"+batchId);
					smsMessage.setBatchId(batchId);
				}
				ps.setString(1, batchId);
				ps.setBoolean(2, smsMessage.getReqReport());
				ps.setObject(3, smsMessage.getSendTime());
				ps.setString(4, smsMessage.getSourceAddr());
				ps.setString(5, smsMessage.getDestAddr());
				ps.setString(6, smsMessage.getContent());
				ps.setString(7, smsMessage.getSmsParam());
				ps.addBatch();
			}
			ps.executeBatch();
			conn.commit();
		} catch (SQLException e) {
			try {
				if (conn!=null){
					conn.rollback();
				}
			} catch (SQLException e1) {
			}
			throw new RuntimeException("发送短信失败!",e);
		}finally{
			if(ps !=null){
				try {
					ps.close();
				} catch (Exception e) {
				}
			}
		}
	}
	
	/**
	 * @see com.touch.sysif.sms.api.SmsClient#getSmsMessage()
	 */
	public SmsMessage[] getSmsMessage() {
		initConn();
		return getSmsMessage(apiId);
	}
	
	/**
	 * @param apiId
	 * @return
	 */
	public synchronized SmsMessage[] getSmsMessage(String apiId) {
		Statement st = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		List<SmsMessage> messageList = new ArrayList<SmsMessage>();
		try {
			st = this.conn.createStatement();
			rs = st.executeQuery("select batchId, sendTime, sourceAddr, destAddr, content, smsParam from sms_api_mo_"+apiId+" limit 100");
			ps = this.conn.prepareStatement("delete from sms_api_mo_"+apiId+" where batchId = ?");
			while (rs.next()) {
				String batchId = rs.getString("batchId");
				messageList.add(new SmsMessage(
						batchId,
						rs.getString("content"),
						rs.getString("destAddr"),
						false,
						rs.getTimestamp("sendTime"),
						rs.getString("smsParam"),
						rs.getString("sourceAddr")
				));
				ps.setString(1, batchId);
				ps.addBatch();
			}
			if(!messageList.isEmpty()){
				ps.executeBatch();
			}
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(rs !=null){
				try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			if(st !=null){
				try {
					st.close();
				} catch (SQLException e) {
				}
			}
			if(ps !=null){
				try {
					ps.close();
				} catch (Exception e) {
				}
			}
		}
		return messageList.toArray(new SmsMessage[messageList.size()]);
	}
	
	public SmsReport[] getSmsReports(String batchId, String destAddr) {
		return getSmsReports(apiId, batchId, destAddr);
	}
	
	public synchronized SmsReport[] getSmsReports(String apiId,String batchId, String destAddr) {
		initConn();
		List<SmsReport> rpstList = new ArrayList<SmsReport>();
		String queryRptSql = "select `batchId`, `destAddr`, `sourceAddr`, `statusCode`, `statusText`, `receivedTime` from sms_api_rpt_" + apiId;
		if(batchId !=null){
			queryRptSql += " where batchId=? ";
			if(destAddr !=null){
				queryRptSql += " and destAddr=? ";
			}
		}
		queryRptSql +=" limit 10 for update";
		String deleteRptSql = "delete from  sms_api_rpt_" + apiId +" where  batchId=? and destAddr=? ";
		PreparedStatement queryRptPs = null;
		PreparedStatement deleteRptPs = null;
		ResultSet rptRs = null;
		try {
			queryRptPs = this.conn.prepareStatement(queryRptSql);
			deleteRptPs = this.conn.prepareStatement(deleteRptSql);
			if(batchId !=null){
				queryRptPs.setString(1, batchId);
				if(destAddr !=null){
					queryRptPs.setString(2, destAddr);
				}
			}
			rptRs = queryRptPs.executeQuery();
			
			while (rptRs.next()) {
				batchId = rptRs.getString("batchId");
				destAddr = rptRs.getString("destAddr");
				rpstList.add(new SmsReport(
						batchId,
						destAddr,
						null,
						rptRs.getTimestamp("receivedTime"),
						rptRs.getString("sourceAddr"),
						rptRs.getInt("statusCode"),
						rptRs.getString("statusText")
				)); 
				deleteRptPs.setString(1, batchId);
				deleteRptPs.setString(2, destAddr);
				deleteRptPs.addBatch();
			}
			if(!rpstList.isEmpty()){
				deleteRptPs.executeBatch();
			}
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			
		}finally{
			if(rptRs !=null){
				try {
					rptRs.close();
				} catch (SQLException e) {
				}
			}
			if(queryRptPs !=null ){
				try {
					queryRptPs.close();
				} catch (SQLException e) {
				}
			}
			if(deleteRptPs !=null){
				try {
					deleteRptPs.close();
				} catch (Exception e) {
				}
			}
		}
		for (SmsReport smsReport : rpstList) {
			loadSubMessage(apiId,smsReport);
		}
		return rpstList.toArray(new SmsReport[rpstList.size()]);
	}
	
	private void loadSubMessage(String apiId,SmsReport smsReport){
		initConn();
		String batchId = smsReport.getBatchId();
		String destAddr = smsReport.getDestAddr();
		
		List<SubSmsMessage> rpstList = new ArrayList<SubSmsMessage>();
		String querySubMsgSql = "select `batchId`, `destAddr`, `sourceAddr`, `content`, `pkTotal`, `pkNumber`, `statusCode` from sms_api_submsg_" + apiId;
		querySubMsgSql += " where batchId=?  and destAddr=? for update";
		
		String deleteSubMsgSql = "delete from  sms_api_submsg_" + apiId +" where  batchId=? and destAddr=? and pkNumber = ?";
		PreparedStatement querySubMsgPs = null;
		PreparedStatement deleteSubMsgPs = null;
		ResultSet subMsgRs = null;
		try {
			querySubMsgPs = this.conn.prepareStatement(querySubMsgSql);
			deleteSubMsgPs = this.conn.prepareStatement(deleteSubMsgSql);
			querySubMsgPs.setString(1, batchId);
			querySubMsgPs.setString(2, destAddr);
			subMsgRs = querySubMsgPs.executeQuery();
			
			while (subMsgRs.next()) {
				batchId = subMsgRs.getString("batchId");
				destAddr = subMsgRs.getString("destAddr");
				short pkNumber = subMsgRs.getShort("pkNumber");
				rpstList.add(new SubSmsMessage(
						batchId,
						subMsgRs.getString("content"),
						destAddr,
						pkNumber,
						subMsgRs.getShort("pkTotal"),
						subMsgRs.getString("sourceAddr"),
						subMsgRs.getInt("statusCode")
				));
				deleteSubMsgPs.setString(1, batchId);
				deleteSubMsgPs.setString(2, destAddr);
				deleteSubMsgPs.setShort(3, pkNumber);
				deleteSubMsgPs.addBatch();
			}
			if(!rpstList.isEmpty()){
				deleteSubMsgPs.executeBatch();
			}
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			
		}finally{
			if(subMsgRs !=null){
				try {
					subMsgRs.close();
				} catch (SQLException e) {
				}
			}
			if(querySubMsgPs !=null ){
				try {
					querySubMsgPs.close();
				} catch (SQLException e) {
				}
			}
			if(deleteSubMsgPs !=null){
				try {
					deleteSubMsgPs.close();
				} catch (Exception e) {
				}
			}
		}
		smsReport.setMessage(rpstList.toArray(new SubSmsMessage[rpstList.size()]));
	}
	
	/**
	 * @see com.touch.sysif.sms.api.SmsClient#send(SmsMessage)
	 */
	public String send(SmsMessage message) {
		initConn();
		send(apiId, message);
		return message.getBatchId();
	}
	
	@Override
	public void shutdown() {
		super.shutdown();
		if(this.conn !=null){
			try {
				conn.close();
			} catch (SQLException e) {
			}
		}
	}
}
